layout: default title: Tasks nav_order: 1

[toc]

Introduction

Intro

Getting familiar with basic objects in Excel

  • Open new workbook

    Choose File -> New in the Excel menu or press Ctrl+N (Cmd+N on Mac)

    image-20210926205209032

  • Create new worksheet.

    In the opened workbook click on a plus in the bottom panel on the left

    image-20210926205404343

  • Read more about difference between Workbook and Worksheet objects [SOURCE]

Data Types

Switching between data types

Most of the data is defined as a General type, unless specified otherwise. To learn what type a specific column is, select the range of values and check the dropdown list value in the Home tab.

Hint: the default alignment used by Excel suggests the data type too: numbers are aligned to the right, while text is aligned to the left.

  • What type is Group column?

  • What type is District Code column?

  • Change District Code column to be Text.

    Select values in District Code column (or select all the values in the column by clicking on the A in the column names pane) and then select Text instead of General

    image-20210926211001795

Number as dates

How to avoid having numbers stored as dates

  • Try to put 1-20 in the first row of the Group-District Code column

  • Select filled cell (D5) and change type to be Text. Did it work?

    Unfortunately, Excel does not allow to switch between dates and text after the transformation has been done. However, we may prevent the automatic switch to the date format by choosing the format of column.

  • Delete the value in the cell. Change the Group-District Code column to be Text. Now. fill the first 5 values manually following the pattern (1-20, 2-20,3-20, 4-20,TOTAL-20 ).

Autofill

How to fill values based on the pattern

We now can fill the rest of the patern using Autofill option.

Hint: you can also use CONCAT formula to fill the pattern using values from Group and District Name columns. Please note, that in this case you need to change column type to be General for the formula to work.

  • Select first 5 rows of Group-District Code column and drag the fill handle down

    image-20210926212807828

Sorting

Explore existing sorting options

  • Sort data by the total number of students (ascending order).

    Select the Number of Students column header (cell E4) and click Sort A to Z in the Data menu tab.

    Hint: Applying Filter allows to use different sorting options as well

    image-20210926213258804

  • Add another level of sorting, District Code.

    Follow the previous step now for District Code column, or use Sort option in the Data menu to add several rules and change their hierarchies

    image-20210926213913533

Filter

Removing errors using Filter

  • Apply Filter (located in the Data menu) to the District Code column;

    image-20210926214341684

  • Select rows with blank District Code;

    image-20210926214441951

  • Right click on selected rows and select option “Delete Rows”;

    image-20210926214539172

  • Make sure you do not have any blanks left

    image-20210926214706616

Find and replace

Working with missing or masked values

  • Use Replace to find all “Msk” entries

    In the Find&Select option of main menu select Replace option or click Ctrl+H (both for PC and Mac);

    image-20210926214917173

  • Replace “Msk” with blank.

    Do not put anything in the Replace field, not even a space

    image-20210926215139163

  • Use Go-To-Special to highlight blank cells

    Select the table area (not including headers) and select Go To Special from the Find&Select options. Click Fill Color to highlight selected cells

    image-20210926215537641

    image-20210926215656596

Other resources:

Filling empty cells using Go To Special: https://spreadsheetplanet.com/fill-blank-cells-with-value-above-in-excel/